import redis
import json
import pymysql
import time
import io
import sys
import datetime

# sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='gb18030')

CONFIG = {
    'redis': {
        'host': '127.0.0.1',
        # 'host': 'magicmall.cc',
        'port': 6379,
        'db': 0
    },
    'mysql': {
        'host': '127.0.0.1',
        # 'host': 'magicmall.cc',
        'port': 3306,
        'user': 'wechat_article',
        'passwd': 'wechat_article',
        'db': 'wechat_article',
        'charset': 'utf8mb4'
    }
}

# REDIS_KEY = "wechat_article2019-12-15"
REDIS_KEY = 'wechat_article' + datetime.datetime.today().strftime("%Y-%m-%d")
ERROR_LOG_FILE = "./" + datetime.datetime.today().strftime("%Y-%m-%d") + ".error_sql" + ".sql"


def open_error_log(file_path=ERROR_LOG_FILE, mode='a'):
    try:
        f = open(file_path, mode)
    except Exception as e:
        print("错误日志文件打开失败")
        print(e)
        sys.exit()
    else:
        return f


def redis_connect():
    try:
        redis_c = redis.Redis(
            host=CONFIG['redis']['host'],
            port=CONFIG['redis']['port'],
            db=CONFIG['redis']['db']
        )
    except Exception as e:
        print("Redis 连接失败")
        print(e)
        sys.exit()
    else:
        return redis_c


def mysql_connect():
    try:
        mysql_c = pymysql.connect(
            host=CONFIG['mysql']['host'],
            port=CONFIG['mysql']['port'],
            user=CONFIG['mysql']['user'],
            passwd=CONFIG['mysql']['passwd'],
            db=CONFIG['mysql']['db'],
            charset=CONFIG['mysql']['charset'],
        )

    except Exception as e:
        print("MySQL 连接失败")
        print(e)
        sys.exit()
    else:
        return mysql_c, mysql_c.cursor()


def read_data_form_redis(redis_c, start_index=0):
    return redis_c.lindex(REDIS_KEY, start_index)


def generate_data(comm_msg_info, app_msg_ext_info):
    return {
        'a_id': comm_msg_info.get('id'),
        'fakeid': comm_msg_info.get('fakeid'),
        'datetime': comm_msg_info.get('datetime'),
        'status': comm_msg_info.get('status'),
        'type': comm_msg_info.get('type'),
        'title': app_msg_ext_info.get('title'),
        'audio_fileid': app_msg_ext_info.get('audio_fileid'),
        'author': app_msg_ext_info.get('author'),
        'content': app_msg_ext_info.get('content'),
        'content_url': app_msg_ext_info.get('content_url'),
        'copyright_stat': app_msg_ext_info.get('copyright_stat'),
        'cover': app_msg_ext_info.get('cover'),
        'del_flag': app_msg_ext_info.get('del_flag'),
        'digest': app_msg_ext_info.get('digest'),
        'duration': app_msg_ext_info.get('duration'),
        'fileid': app_msg_ext_info.get('fileid'),
        'item_show_type': app_msg_ext_info.get('item_show_type'),
        'malicious_content_type': app_msg_ext_info.get('malicious_content_type'),
        'malicious_title_reason_id': app_msg_ext_info.get('malicious_title_reason_id'),
        'play_url': app_msg_ext_info.get('play_url'),
        'source_url': app_msg_ext_info.get('source_url')
    }


def parse_data(article_list):
    res_data = []
    general_msg_list = json.loads(article_list)['general_msg_list']
    lists = json.loads(general_msg_list)['list']
    for article_list in lists:
        comm_msg_info = article_list.get('comm_msg_info')
        app_msg_ext_info = article_list.get('app_msg_ext_info')
        if app_msg_ext_info:
            res_data.append(generate_data(comm_msg_info, app_msg_ext_info))
            if app_msg_ext_info['multi_app_msg_item_list']:
                for multi_app_msg_item in app_msg_ext_info['multi_app_msg_item_list']:
                    res_data.append(generate_data(comm_msg_info, multi_app_msg_item))
    return res_data


def is_exist(title, author, error_log_f):
    sql = "select * from wechat_article where title = %s and author = %s"
    try:
        mysql_cursor.execute(sql, (title, author))
    except Exception as e:
        write_wrong_sql(error_log_f, sql)
        print(e)
    else:
        return mysql_cursor.rowcount


def save_to_mysql(p_data_s, error_log_f):
    for p_data in p_data_s:
        if not is_exist(p_data['title'], p_data['author'], error_log_f):
            sql = "insert into wechat_article values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            try:
                mysql_cursor.execute(sql, (
                    None,
                    p_data['a_id'],
                    p_data['fakeid'],
                    p_data['status'],
                    p_data['type'],
                    p_data['audio_fileid'],
                    p_data['author'],
                    p_data['content'],
                    p_data['content_url'],
                    p_data['copyright_stat'],
                    p_data['cover'],
                    p_data['del_flag'],
                    p_data['digest'],
                    p_data['duration'],
                    p_data['fileid'],
                    p_data['item_show_type'],
                    p_data['malicious_content_type'],
                    p_data['malicious_title_reason_id'],
                    p_data['play_url'],
                    p_data['source_url'],
                    p_data['datetime'],
                    p_data['title'],
                ))
            except Exception as e:
                write_wrong_sql(error_log_f, sql)
                print(e)
    mysql_con.commit()


def write_wrong_sql(e_log_f, sql):
    e_log_f.write(sql)


if __name__ == '__main__':
    redis_con = redis_connect()
    mysql_con, mysql_cursor = mysql_connect()
    error_log_f = open_error_log()
    try:
        print('Program Start...')
        start_index = 1
        data = read_data_form_redis(redis_con, start_index)
        while data:
            p_data = parse_data(data)
            save_to_mysql(p_data, error_log_f)
            start_index += 1
            data = read_data_form_redis(redis_con, start_index)
            print('now_index: ', start_index)
    except Exception as e:
        print(e)
    else:
        print('Program end...')
    finally:
        redis_con.close()
        mysql_cursor.close()
        mysql_con.close()
